#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from pymysql.err import OperationalError, ProgrammingError
import pymysql
import os
import configparser

# ==================== 读取 db_config.ini 文件设置 ==================== #

base_dir = os.path.dirname(os.path.abspath(__file__))
config_file_path = os.path.join(base_dir, 'db_config.ini')

cf = configparser.ConfigParser()
cf.read(config_file_path)

host = cf.get('mysqlconf', 'host')
port = int(cf.get('mysqlconf', 'port'))
db = cf.get('mysqlconf', 'db_name')
user = cf.get('mysqlconf', 'user')
password = cf.get('mysqlconf', 'password')

# ======================== 封装 MySQL 基本操作 ======================== #


class MySqlDB:

    def __init__(self, connect_attempts=5):
        for attempt in range(connect_attempts):
            try:
                self.conn = pymysql.connect(host=host, port=port, user=user, password=password, db=db,
                                            charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
                print('Connect to MySQL DB successfully')
                self.cursor = self.conn.cursor()
                break
            except OperationalError as e:
                print('NO.{0} attempt: Fail to connect MySQL: {1}'.format(connect_attempts + 1, str(e)[:-1]))

    def exec_sql(self, sql_cmd):
        try:
            self.cursor.execute(sql_cmd)
            results = self.cursor.fetchall()
            return results
        except ProgrammingError as e:
            print(str(e)[:-1])
            return None

    def close(self):
        self.conn.close()


if __name__ == '__main__':
    db = MySqlDB()
    sql = "SELECT * FROM sign_event WHERE sign_event.`name` LIKE '%小龙虾%'"
    a = db.exec_sql(sql)
    print(a)
    db.close()
